package com.example.myapplication;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.util.Log;

import androidx.annotation.NonNull;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDAO {
    private DBHelper helper;
    private SQLiteDatabase db;

    public UserDAO(Context context){

        helper=new DBHelper(context,null,1);
        db=helper.getWritableDatabase();
    }
    //添加数据->Thing
    public long addThing(Thing thing){

        ContentValues values=new ContentValues();
        values.put("name",thing.getName());
        values.put("count",thing.getCount());
        values.put("unit",thing.getUnit());
        values.put("type1",thing.getTypeFirst());
        values.put("imagepath",thing.getImagePath());
        values.put("position",thing.getPosition());
        values.put("information",thing.getInformation());
        values.put("date",thing.getDatetime());
        //values.
        db.beginTransaction();
        long i=db.insert("Thing",null,values);
        db.setTransactionSuccessful();
        db.endTransaction();
        return i;
    }
    //更新数据-》Thing
    public int updateThing(Thing thing){
        ContentValues values=new ContentValues();
        values.put("name",thing.getName());
        values.put("count",thing.getCount());
        values.put("unit",thing.getUnit());
        values.put("type1",thing.getTypeFirst());
        values.put("imagepath",thing.getImagePath().toString());
        values.put("position",thing.getPosition());
        values.put("information",thing.getInformation());
        values.put("date",thing.getDatetime());
        db.beginTransaction();
        int i=db.update("Thing",values,"id=?",new String[]{String.valueOf(thing.getId())});
        db.setTransactionSuccessful();
        db.endTransaction();
        return i;
    }
    //删除数据-》THing
    public void deleteThing(int id){
        db.beginTransaction();
        db.execSQL("delete from Thing where id=? ",new String[]{String.valueOf(id)});
        db.setTransactionSuccessful();
        db.endTransaction();
    }
    //查询全部Thing
    public List<Thing> queryThingAll(){
        db.beginTransaction();
        List<Thing> list=new ArrayList<>();
        Cursor cursor=db.query("Thing",null,null,null,null,null,null);
        list=convertFromCursor(cursor);
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        return list;
    }
    //处理查询出的Thing结果
    private List<Thing> convertFromCursor(Cursor cursor){
        List<Thing> list=new ArrayList<>();
        if(cursor!=null&&cursor.moveToFirst()){
            do{
                @SuppressLint("Range")
                int id=cursor.getInt(cursor.getColumnIndex("id"));
                @SuppressLint("Range")
                String name= cursor.getString(cursor.getColumnIndex("name"));
                @SuppressLint("Range")
                int count= cursor.getInt(cursor.getColumnIndex("count"));
                @SuppressLint("Range")
                int unit= cursor.getInt(cursor.getColumnIndex("unit"));
                @SuppressLint("Range")
                int type1= cursor.getInt(cursor.getColumnIndex("type1"));
                @SuppressLint("Range")
                String imagepath= cursor.getString(cursor.getColumnIndex("imagepath"));
                @SuppressLint("Range")
                String position= cursor.getString(cursor.getColumnIndex("position"));
                @SuppressLint("Range")
                String information= cursor.getString(cursor.getColumnIndex("information"));
                @SuppressLint("Range")
                long datetime = cursor.getLong(cursor.getColumnIndex("date"));
                list.add(new Thing(id,name,count,unit,type1, imagepath,position,information, datetime));
            }while(cursor.moveToNext());
        }
        return list;
    }

    private String longToDateString(long dateTime) {
        SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日");
        Date date = new Date(dateTime);
        return formater.format(date);
    }

    //清空Thing
    public void clearThing(){
        String DELETE_ALL_THING="delete from Thing";
        db.beginTransaction();
        db.execSQL(DELETE_ALL_THING);
        db.execSQL("UPDATE sqlite_sequence SET seq = 0 WHERE name ='Thing'");//Thing表id归零
        db.setTransactionSuccessful();
        db.endTransaction();
    }
    //给unit表添加初始数据
    public void initUnit(){
        db.beginTransaction();
        db.execSQL("delete from Unit");
        db.execSQL("UPDATE sqlite_sequence SET seq = 0 WHERE name ='Unit'");//Unit表id归零
        db.execSQL("insert into Unit values (null,'个')");
        db.execSQL("insert into Unit values (null,'件')");
        db.execSQL("insert into Unit values (null,'双')");
        db.execSQL("insert into Unit values (null,'盒')");
        db.execSQL("insert into Unit values (null,'只')");
        db.execSQL("insert into Unit values (null,'包')");
        db.execSQL("insert into Unit values (null,'瓶')");
        db.setTransactionSuccessful();
        db.endTransaction();
    }
    public void insertUnit(String unit){
        db.beginTransaction();
        Cursor cursor=db.rawQuery("select * from Unit where name=?",new String[]{unit});
        //Log.d("输出", "insertUnit: "+cursor.getCount());
        if(cursor.getCount()==0)
            db.execSQL("insert into Unit(name) values(?)",new String[]{unit});
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
    }
    public void initTypeFirst(){
        db.beginTransaction();
        db.execSQL("delete from TypeFirst");
        db.execSQL("UPDATE sqlite_sequence SET seq = 0 WHERE name ='TypeFirst'");//表id归零
        db.execSQL("insert into TypeFirst(name) values('未分类')");
        db.execSQL("insert into TypeFirst(name) values('服装')");
        db.execSQL("insert into TypeFirst(name) values('电子产品')");
        db.execSQL("insert into TypeFirst(name) values('纸质印刷品')");
        db.execSQL("insert into TypeFirst(name) values('容器')");
        db.setTransactionSuccessful();
        db.endTransaction();
    }


    @SuppressLint("Range")
    public void insertTypeFirst(String type){
        db.beginTransaction();
        Cursor cursor=db.rawQuery("select * from TypeFirst where name=?",new String[]{type});
        if (cursor.getCount()==0){
            db.execSQL("insert into TypeFirst(name) values(?)",new String[]{type});
        }
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    public void closeDB(){
        db.close();
    }

    public List<String> queryUnitAll() {
        db.beginTransaction();
        List<String> list=new ArrayList<>();
        Cursor cursor=db.query("Unit",null,null,null,null,null,null);
        if(cursor!=null&&cursor.moveToFirst()){
            do{
                @SuppressLint("Range")
                String unit=cursor.getString(cursor.getColumnIndex("name"));
                list.add(unit);
            }while(cursor.moveToNext());
        }
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        return list;
    }

    public Thing queryThing(int thingid) {
        Thing thing=new Thing();
        db.beginTransaction();
        Cursor cursor=db.rawQuery("select * from Thing where id=?",new String[]{String.valueOf(thingid)});
        if(cursor!=null&&cursor.moveToFirst()){
            thing = getThingFromCursor(cursor);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        return thing;
    }

    @NonNull
    private static Thing getThingFromCursor(Cursor cursor) {
        Thing thing;
        @SuppressLint("Range")
        int id= cursor.getInt(cursor.getColumnIndex("id"));
        @SuppressLint("Range")
        String name= cursor.getString(cursor.getColumnIndex("name"));
        @SuppressLint("Range")
        int count= cursor.getInt(cursor.getColumnIndex("count"));
        @SuppressLint("Range")
        int unit= cursor.getInt(cursor.getColumnIndex("unit"));
        @SuppressLint("Range")
        int type1= cursor.getInt(cursor.getColumnIndex("type1"));
        @SuppressLint("Range")
        String imagepath= cursor.getString(cursor.getColumnIndex("imagepath"));
        @SuppressLint("Range")
        String position= cursor.getString(cursor.getColumnIndex("position"));
        @SuppressLint("Range")
        String information= cursor.getString(cursor.getColumnIndex("information"));
        @SuppressLint("Range")
        long datetime = cursor.getLong(cursor.getColumnIndex("date"));
        thing=new Thing(id,name,count,unit,type1,imagepath,position,information, datetime);
        return thing;
    }

    public List<String> queryType1All() {
        db.beginTransaction();
        List<String> list=new ArrayList<>();
        Cursor cursor=db.query("TypeFirst",null,null,null,null,null,null);
        if(cursor!=null&&cursor.moveToFirst()){
            do{
                @SuppressLint("Range")
                String type=cursor.getString(cursor.getColumnIndex("name"));
                list.add(type);
            }while(cursor.moveToNext());
        }
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        return list;
    }
    @SuppressLint("Range")
    public Boolean queryIsFirstUse(){
        Boolean res=false;
        db.beginTransaction();
        Cursor cursor=db.rawQuery("select * from IsFirstUse where id=1",null);
        if(cursor!=null&&cursor.moveToFirst()){
            res=cursor.getInt(cursor.getColumnIndex("isfirstuse"))>0;
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        return res;
    }
    public void updateIsFurstUse(boolean value){
        db.beginTransaction();
        if(value)
            db.execSQL("update [IsFirstUse] set isfirstuse=1 where id=1");
        else
            db.execSQL("update [IsFirstUse] set isfirstuse=0 where id=1");
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    public List<Thing> queryThingByName(String key) {
        db.beginTransaction();
        List<Thing>list=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from Thing where name LIKE ?",new String[]{"%"+key+"%"});
        if(cursor!=null&&cursor.moveToFirst()){
            do{
                @SuppressLint("Range")
                Thing thing=getThingFromCursor(cursor);
                list.add(thing);
            }while(cursor.moveToNext());
        }
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        return list;
    }
    public List<Thing> queryThingByInformation(String info){
        db.beginTransaction();
        List<Thing>list=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from Thing where name LIKE ?",new String[]{"%"+info+"%"});
        if(cursor!=null&&cursor.moveToFirst()){
            do{
                @SuppressLint("Range")
                Thing thing=getThingFromCursor(cursor);
                list.add(thing);
            }while(cursor.moveToNext());
        }
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        return list;
    }

    @SuppressLint("Range")
    public int queryLastThingId() {
        db.beginTransaction();
        Cursor cursor= db.rawQuery("select * from Thing",null);
        int max=0;
        if (cursor!=null&&cursor.moveToFirst()){
            do {
                if (cursor.getInt(cursor.getColumnIndex("id"))>max)
                    max=cursor.getInt(cursor.getColumnIndex("id"));
            }while (cursor.moveToNext());
        }
        cursor.close();
        db.setTransactionSuccessful();
        db.endTransaction();
        return max;
    }
}
